package net.zjitc.dao.Impl;

import net.zjitc.dao.ProductDao;
import net.zjitc.domain.Evaluate;
import net.zjitc.domain.Product;
import net.zjitc.domain.User;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.PageBean;
import utils.dbutils.C3P0utils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by guo on 2017/11/22.
 */
public class ProductDaoImpl implements ProductDao {

    public PageBean<Product> findPageBean(int page, int pageSize) {
        //pageBean的参数都是什么时候设置进去的，一定要设置，要不页面取不到
        PageBean<Product> pageBean = new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);
        //查总条数
        QueryRunner runner = new QueryRunner(C3P0utils.getDs());
        String sql = "select count(*) from product";
        //要符合条件的
        String ssql = "select * from product order by pid limit ?,?";
        try {
            Number num = (Number) runner.query(sql,new ScalarHandler());
            int count = num.intValue();
            pageBean.setCount(count);
            List<Product>  list = (List<Product>) runner.query(ssql,new BeanListHandler(Product.class),(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);
            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int insert(Product product) {
        QueryRunner runner = new QueryRunner(C3P0utils.getDs());
        String sql = "insert into product(pname,market_price,shop_price,image,pdesc,is_hot,pdate,csid) value(?,?,?,?,?,?,?,?)";
        try {
            int count = runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),
                    product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int delete(int pid) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="delete from product where pid=?";
        int num=0;
        try {
            num= queryRunner.update(sql,pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public Product findProductById(int pid) {
        QueryRunner queryRunner=new QueryRunner(C3P0utils.getDs());
        String sql="select * from product where pid=?";
        Product product=null;
        try {
            product= (Product) queryRunner.query(sql,new BeanHandler(Product.class),pid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return product;
    }

    public int update(Product product) {
        QueryRunner runner = new QueryRunner(C3P0utils.getDs());
        String sql = "update product set pname=?,shop_price=?,market_price=?,is_hot=?,pdesc=?where pid=?";
        int num = 0;
        try {
            num = runner.update(sql,product.getPname(),product.getShop_price(),product.getMarket_price(),product.getIs_hot(),product.getPdesc(),product.getPid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return num;
    }

    public List<Product> findAllHotProduct() {
        QueryRunner runner = new QueryRunner(C3P0utils.getDs());
        String sql = "select * from product where is_hot=1";
        List<Product> list=null;
        try {
            list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Product> findAllNewProduct() {
        QueryRunner runner = new QueryRunner(C3P0utils.getDs());
        String sql = "select * from product order by pdate desc;";
        List<Product> list=null;
        try {
            list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public PageBean<Product> findPageBeanByCid(int page, int pageSize, int cid) {
        PageBean<Product> bean = new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);
        QueryRunner runner = new QueryRunner(C3P0utils.getDs());
        String sqlCount = "select count(*) from product p join categorysecond cs,category c where p.csid=cs.csid and cs.cid = c.cid and c.cid=?";
        String sql = "select p.* from product p join categorysecond cs,category c where p.csid=cs.csid and cs.cid=c.cid and c.cid=? limit ?,?";
        try {
            Number num = (Number) runner.query(sqlCount, new ScalarHandler(), cid);
            bean.setCount(num.intValue());
            List<Product> list = runner.query(sql, new BeanListHandler<Product>(Product.class), cid, (page - 1) * pageSize, pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
    return null;
    }

    public PageBean<Product> findPageByCsid(int page, int pageSize, int csid) {
        PageBean<Product> pageBean=new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);

        QueryRunner runner=new QueryRunner(C3P0utils.getDs());
        String sqlCount="SELECT COUNT(*) FROM product p JOIN categorysecond cs on p.csid = cs.csid  AND cs.csid=? ";

        String sql="select p.* from product p JOIN categorysecond cs on p.csid = cs.csid  and cs.csid=? limit ?,?";
        try {
            //查询数据总数
            Number number= (Number) runner.query(sqlCount,new ScalarHandler(),csid);
            pageBean.setCount(number.intValue());

            //查询数据列表
            List<Product> list= (List<Product>) runner.query(sql,new BeanListHandler(Product.class),csid,(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);

            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }



public List<Evaluate> findEvaluateByPid(int pid) {
    QueryRunner runner = new QueryRunner(C3P0utils.getDs());
    String sql = "Select * from evaluate e join product p ,USER u where e.pid=p.pid and e.uid=u.uid and p.pid=?";
    //存入评价对象集合
    List<Evaluate> evaluates = new ArrayList<Evaluate>();
    //存入Map对象的list集合
    List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
    try {
        list = runner.query(sql, new MapListHandler(), pid);
        for (Map<String, Object> m : list) {
            User user = new User();
            Product product = new Product();
            Evaluate evaluate = new Evaluate();
            BeanUtils.populate(user, m);
            BeanUtils.populate(product, m);
            BeanUtils.populate(evaluate, m);
            evaluate.setUser(user);
            evaluate.setProduct(product);
            evaluates.add(evaluate);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return evaluates;
}
}
